package learning.jdbc.jdbc1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class TestPer {

	public static void main(String[] args) throws Exception {
         //add();
//		delete(7);
//		update(6);
//		query(6);
//		queryAll();
		List<Person> list=queryAll();
		for (Person p : list) {
			System.out.println(p);
		}
	}

	public static void add() throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
         String url="jdbc:sqlite:/c:/Users/imagine/Test001";
         Connection conn = DriverManager.getConnection(url);
         String sql="insert into person(name,sex,age,salary) values('cat',1,13,2200)";
         Statement statement=conn.createStatement();
         System.out.println(statement.execute(sql));
         conn.close();
	}
	public static void delete(int id) throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
         String url="jdbc:sqlite:/c:/Users/imagine/Test001";
         Connection conn = DriverManager.getConnection(url);
         String sql="delete from person where id="+id;
         Statement statement=conn.createStatement();
         System.out.println(statement.execute(sql));
         conn.close();
	}
	public static void update(int id) throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
         String url="jdbc:sqlite:/c:/Users/imagine/Test001";
         Connection conn = DriverManager.getConnection(url);
         String sql="update  person set name='mouse' where id="+id;
         Statement statement=conn.createStatement();
         System.out.println(statement.execute(sql));
         conn.close();
	}
	public static void query(int id) throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
         String url="jdbc:sqlite:/c:/Users/imagine/Test001";
         Connection conn = DriverManager.getConnection(url);
         String sql="select *from person where id="+id;
         Statement statement=conn.createStatement();
         ResultSet rs=statement.executeQuery(sql);
         while(rs.next()){
        	 System.out.print(rs.getInt("id")+"\t");
        	 System.out.print(rs.getString("name")+"\t");
        	 System.out.print(rs.getInt("sex")+"\t");
        	 System.out.print(rs.getInt("age")+"\t");
        	 System.out.print(rs.getDouble("salary"));
        	 
         }
         conn.close();
	}
	public static List<Person> queryAll() throws ClassNotFoundException, SQLException {
		List<Person> list=new ArrayList<Person>();
		Class.forName("org.sqlite.JDBC");
         String url="jdbc:sqlite:/c:/Users/imagine/Test001";
         Connection conn = DriverManager.getConnection(url);
         String sql="select *from person ";
         Statement statement=conn.createStatement();
         ResultSet rs=statement.executeQuery(sql);
         while(rs.next()){
        	 int id=rs.getInt("id");
        	 String name=rs.getString("name");
        	 int sex=rs.getInt("sex");
        	 int age=rs.getInt("age");
        	 double salary=rs.getDouble("salary");
        	 list.add(new Person(id,name,sex,age,salary));
//        	 System.out.print(rs.getInt("id")+"\t");
//        	 System.out.print(rs.getString("name")+"\t");
//        	 System.out.print(rs.getInt("sex")+"\t");
//        	 System.out.print(rs.getInt("age")+"\t");
//        	 System.out.print(rs.getDouble("salary"));
//        	 System.out.println();
         }
         conn.close();
         return list;
	}
}
class Person{
	private int id;
	private String name;
	private int sex;
	private int age;
	private double salary;
	public Person(int id, String name, int sex, int age, double salary) {
		super();
		this.id = id;
		this.name = name;
		this.sex = sex;
		this.age = age;
		this.salary = salary;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getSex() {
		return sex;
	}
	public void setSex(int sex) {
		this.sex = sex;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public double getSalary() {
		return salary;
	}
	public void setSalary(double salary) {
		this.salary = salary;
	}
	@Override
	public String toString() {
		return "Person [id=" + id + ", name=" + name + ", sex=" + sex
				+ ", age=" + age + ", salary=" + salary + "]";
	}
	
}














